﻿using B.S.BaseData.infrastructure;
using B.S.DataBase.Traceability.Infrastructure;
using B.S.DataBase.Traceability.Infrastructure.Interface;
using Dapper;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace B.S.DataBase.Traceability.Infrastructure.Implement
{
    /// <summary>
    /// 实现仓储接口
    /// </summary>
    public class BaseRepository<T> : IBaseRepository<T> where T : class
    {
        private readonly string _connectionString;

        public BaseRepository(SCDnconexet db, ILogger<BaseRepository<T>> logger)
        {
            _connectionString = db.Database.GetConnectionString();
        }
        // 其他接口方法实现...
        public Task<int> Update(T entity)
        {
            // 实现具体更新逻辑
            return Task.FromResult(0);
        }

        public Task<int> AddRanges(List<T> entitys)
        {
            // 实现具体批量添加逻辑
            return Task.FromResult(0);
        }

        public Task<int> DeleteRemove(List<T> entity)
        {
            // 实现具体批量删除逻辑
            return Task.FromResult(0);
        }

        public Task<int> Delete(T entitys)
        {
            // 实现具体删除逻辑
            return Task.FromResult(0);
        }

        public Task<int> UpdateRanhes(List<T> entitys)
        {
            // 实现具体批量修改逻辑
            return Task.FromResult(0);
        }

        public IEnumerable<T> GetAllRanhes()
        {
            // 实现具体查询逻辑
            return Enumerable.Empty<T>();
        }

        // 异步查询单个实体
        public async Task<T> GetAsync(string sql, object param = null)
        {
            try
            {
                using (var connection = new SqlConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    return await connection.QueryFirstOrDefaultAsync<T>(sql, param);
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        // 原有方法
        public async Task<IEnumerable<T>> GetAllAsync(string sql, object param = null)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                await connection.OpenAsync();
                return await connection.QueryAsync<T>(sql, param);
            }
        }

        // 新增泛型方法
        public async Task<IEnumerable<TReturn>> GetAllAsync<TReturn>(string sql, object param = null)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                await connection.OpenAsync();
                return await connection.QueryAsync<TReturn>(sql, param);
            }
        }
        // 实现标量查询方法
        public async Task<TResult> ExecuteScalarAsync<TResult>(string sql, object param = null)
        {
            try
            {
                using (var connection = new SqlConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    return await connection.ExecuteScalarAsync<TResult>(sql, param);
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        // 异步插入操作
        public async Task<int> InsertAsync(string sql, object param = null)
        {
            try
            {
                using (var connection = new SqlConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    return await connection.ExecuteAsync(sql, param);
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        // 异步更新操作
        public async Task<int> UpdateAsync(string sql, object param = null)
        {
            try
            {
                using (var connection = new SqlConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    return await connection.ExecuteAsync(sql, param);
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        // 异步删除操作
        public async Task<int> DeleteAsync(string sql, object param = null)
        {
            try
            {
                using (var connection = new SqlConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    return await connection.ExecuteAsync(sql, param);
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        // 批量插入（无事务）
        public async Task<int> InsertRangeAsync(IEnumerable<T> entities)
        {
            try
            {
                using (var connection = new SqlConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    return await connection.ExecuteAsync("INSERT INTO YourTable (...) VALUES (@...)", entities);
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        // 批量更新（无事务）
        public async Task<int> UpdateRangeAsync(IEnumerable<T> entities)
        {
            try
            {
                using (var connection = new SqlConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    return await connection.ExecuteAsync("UPDATE YourTable SET ... WHERE ...", entities);
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        // 批量删除（无事务）
        public async Task<int> DeleteRangeAsync(IEnumerable<T> entities)
        {
            try
            {
                using (var connection = new SqlConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    return await connection.ExecuteAsync("DELETE FROM YourTable WHERE ...", entities);
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        // 批量插入（带事务）
        public async Task<int> InsertRangeWithTransactionAsync(IEnumerable<T> entities)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var transaction = connection.BeginTransaction())
                {
                    try
                    {
                        var rowsAffected = await connection.ExecuteAsync(
                            "INSERT INTO YourTable (...) VALUES (@...)",
                            entities,
                            transaction);
                        await transaction.CommitAsync();
                        return rowsAffected;
                    }
                    catch (Exception ex)
                    {
                        await transaction.RollbackAsync();
                        throw;
                    }
                }
            }
        }

        // 批量更新（带事务）
        public async Task<int> UpdateRangeWithTransactionAsync(IEnumerable<T> entities)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var transaction = connection.BeginTransaction())
                {
                    try
                    {
                        var rowsAffected = await connection.ExecuteAsync(
                            "UPDATE YourTable SET Column1 = @Column1 WHERE Id = @Id",
                            entities,
                            transaction);
                        await transaction.CommitAsync();
                        return rowsAffected;
                    }
                    catch (Exception ex)
                    {
                        await transaction.RollbackAsync();
                        throw;
                    }
                }
            }
        }
        // 批量删除（带事务）
        public async Task<int> DeleteRangeWithTransactionAsync(IEnumerable<T> entities, Func<T, object> getId)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var transaction = connection.BeginTransaction())
                {
                    try
                    {
                        var ids = entities.Select(getId).ToArray();
                        var rowsAffected = await connection.ExecuteAsync(
                            "DELETE FROM YourTable WHERE Id IN @Ids",
                            new { Ids = ids },
                            transaction);
                        await transaction.CommitAsync();
                        return rowsAffected;
                    }
                    catch (Exception ex)
                    {
                        await transaction.RollbackAsync();
                        throw;
                    }
                }
            }


        }
    }
}